﻿using MySql.Data.MySqlClient;
using System;
using System.Data;
using System.Windows.Forms;

namespace idataplatform
{
    class conncls
    { 
        //public static string Myconn = "server=119.145.166.122;port=49177;user=root;password=zssk1979;database=sydb;"; //根据自己的设置
        
        private MySqlConnection sqlconn = new MySqlConnection();    //定义数据库连接变量
        //构造函数,建立数据库连接
        public conncls()
        {
            if (sqlconn.State == ConnectionState.Closed)
            {
                //链接数据库字符串
                sqlconn.ConnectionString = "server=119.145.166.122;port=49177;user=root;password=zssk1979;database=sydb;";
                sqlconn.Open();
            }
        }

        public DataTable Dts(string SQLstr)
        {

            DataTable ndt = new DataTable();

            //MySqlConnection sqlconn = new MySqlConnection(Myconn);
            //sqlconn.Open();

            MySqlCommand mycmd = sqlconn.CreateCommand();
            mycmd.CommandText = SQLstr;

            MySqlDataAdapter myadp = new MySqlDataAdapter(mycmd);

            try
            {
                myadp.Fill(ndt);
                return ndt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            finally
            {
                sqlconn.Close();
                sqlconn.Dispose();
                myadp.Dispose();
            }
        }

        public int rescount(string SQLstr)
        {
            DataTable ndt = new DataTable();

            int nums = 0;

            //MySqlConnection sqlconn = new MySqlConnection(Myconn);
            //sqlconn.Open();

            MySqlCommand mycmd = sqlconn.CreateCommand();
            mycmd.CommandText = SQLstr;

            MySqlDataAdapter myadp = new MySqlDataAdapter(mycmd);

            try
            {
                myadp.Fill(ndt);
                nums = ndt.Rows.Count;
                return nums;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            finally
            {
                sqlconn.Close();
                sqlconn.Dispose();
                myadp.Dispose();
            }
        }

        public int varInt(string SQLstr)
        {
            DataTable ndt = new DataTable();

            int i = 0;

            //MySqlConnection sqlconn = new MySqlConnection(Myconn);
            //sqlconn.Open();

            MySqlCommand mycmd = sqlconn.CreateCommand();
            mycmd.CommandText = SQLstr;

            MySqlDataAdapter myadp = new MySqlDataAdapter(mycmd);

            try
            {
                myadp.Fill(ndt);
                if (ndt.Rows[0][0].ToString() != "")
                {
                    i = Convert.ToInt32(ndt.Rows[0][0].ToString());
                }
                return i;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            finally
            {
                sqlconn.Close();
                sqlconn.Dispose();
                myadp.Dispose();
            }
        }

        public string varStr(string SQLstr)
        {
            DataTable ndt = new DataTable();

            string s = "";

            //MySqlConnection sqlconn = new MySqlConnection(Myconn);
            //sqlconn.Open();

            MySqlCommand mycmd = sqlconn.CreateCommand();
            mycmd.CommandText = SQLstr;

            MySqlDataAdapter myadp = new MySqlDataAdapter(mycmd);

            try
            {
                myadp.Fill(ndt);
                if (ndt.Rows.Count > 0)
                {
                    s = ndt.Rows[0][0].ToString();
                }
                return s;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            finally
            {
                sqlconn.Close();
                sqlconn.Dispose();
                myadp.Dispose();
            }
        }

        public void sqlexec(string SQLstr)
        {
            //MySqlConnection sqlconn = new MySqlConnection(Myconn);
            //sqlconn.Open();

            MySqlCommand mycmd = sqlconn.CreateCommand();
            mycmd.CommandText = SQLstr;

            MySqlDataAdapter myadp = new MySqlDataAdapter(mycmd);

            try
            {
                mycmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            finally
            {
                sqlconn.Close();
                sqlconn.Dispose();
                mycmd.Dispose();
            }
        }
        //执行SQL语句,对数据库进行“添加”、“删除”、“更改”
        public void ExcSql(string Sql)
        {
            try
            {
                MySqlCommand Cmd = new MySqlCommand(Sql, sqlconn);
                Cmd.ExecuteNonQuery();
            }
            catch
            {
                MessageBox.Show("输入数据有误码，请重新输入！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
        }
    }
}
